#!/user/bin/env python3
# -*- coding: utf-8 -*-
"""
--------------------------------------
    Author:     JiChao_Song
    Date  :     2022/1/12 15:30
    Desc  :
--------------------------------------
"""
import json

import pymssql
import pymysql
from pymysql.cursors import DictCursor

from common.utils.log_utils import logger

db_host = '114.67.89.195'
db_port = '1433'
db_user = 'readuser'
db_pwd = 'READ433$SKUSER'
# db_name = 'MSRosterDB'
db_server = 'windows'


class CommonSqlConfig:

    def __init__(self, configType: int, host: str, port: int, user: str, password: str, database: str):
        self.configType = configType
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.server: str = 'windows'
        self.pool = None

        self.connected = False

    def createMssqlPool(self):
        try:
            logger.info(f"""
createMssqlPool: 
password = {self.password},
user = {self.user},
database = {self.database},
port = {self.port},
host = {self.host}
            """)
            self.pool = pymssql.connect(
                server = self.server,
                host = self.host,
                port = self.port,
                user = self.user,
                password = self.password,
                database = self.database,
                as_dict = True,
                charset = "utf8",
            ).cursor()
        except Exception as e:
            logger.error(f'数据链接异常: {e}')
            self.connected = False
        else:
            self.connected = True

    def createMysqlPool(self):
        try:
            logger.info(f"""
createMssqlPool: 
password = {self.password},
user = {self.user},
database = {self.database},
port = {self.port},
host = {self.host}
            """)
            self.pool = pymysql.connect(
                user = self.user,
                password = self.password,
                host = self.host,
                port = self.port,
                database = self.database,
                charset = "utf8",
            ).cursor()
        except Exception as e:
            logger.error(f'数据连接异常: {e}')
            self.connected = False
        else:
            self.connected = True


class CommonBaseMapper():

    def __init__(self, pool):
        self.pool = pool

    def databaseList(self):
        """数据库列表"""
        pass

    def tableList(self, dbId: str):
        """表列表"""
        pass

    def columnList(self, dbName: str, tableName: str):
        """字段类表"""
        pass

    def indexList(self, dbName: str, tableName: str, objectId: int, indexId: int):
        """索引列表"""
        pass


class MssqlMapper(CommonBaseMapper):

    def databaseList(self):
        sql = """
        select database_id, [name]
        from sys.databases
        where [name] not in ('master', 'tempdb', 'model', 'msdb', 'sysdb')
        order by [name]
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        database_list = []
        for i in rows:
            database_list.append(i)
        self.pool.close()
        return database_list

    def tableList(self, dbId: str):
        sql = f"""
        select a.[name] tableName, cast(b.value as nvarchar(max)) tableComment
        from {dbId}.sys.objects a
        left join MSRosterDB.sys.extended_properties b
        on b.major_id = a.OBJECT_ID and b.minor_id = 0 and b.name = 'MS_Description'
        where [type] = 'U'
        and a.name <> 'sysdiagrams'
        order by a.[name]
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        self.pool.close()
        return rows

    def columnList(self, dbName: str, tableName: str):
        sql = f"""
        select distinct a.column_id,
                        a.name       as                    columnname,
                        c.name       as                    typename,
                        a.max_length as                    typelength,
                        cast(d.value as nvarchar(max))     comment,
                        cast(ISNULL(e.index_id, 0) as bit) isPrimaryKey,
                        a.is_nullable                      isNullAble,
                        a.is_identity                      isIdentity,
                        a.[precision],
                        a.scale,
                        cast(f.definition as varchar(200)) defalutDefine
        from {dbName}.sys.columns a
                 inner join {dbName}.sys.types c on c.user_type_id = a.user_type_id
                 left join {dbName}.sys.extended_properties d
                           on d.major_id = OBJECT_ID('{dbName}..{tableName}') and a.column_id = d.minor_id and d.class = 1
                 left join {dbName}.[sys].[index_columns] e
                           on e.object_id = OBJECT_ID('{dbName}..{tableName}') and e.column_id = a.column_id and e.index_id = 1
                 left join {dbName}.sys.default_constraints f
                           on a.object_id = f.parent_object_id and f.parent_column_id = a.column_id
        where a.object_id = OBJECT_ID('{dbName}..{tableName}')
        order by a.column_id
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        self.pool.close()
        return rows

    def indexList(self, dbName: str, tableName: str, objectId: int = None, indexId: int = None):
        sql = f"""
        select 
            *,
            [name],
            object_id objectId,
            index_id indexId,
            [type_desc] typeDesc,
            is_unique isUnique,
            [ignore_dup_key] ignoreDupKey, 
            is_primary_key isPrimaryKey,
            filter_definition filterDefinition 
        from {dbName}.sys.indexes
        where OBJECT_ID('{dbName}..{tableName}') = object_id
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        self.pool.close()
        return rows


class MysqlMapper(CommonBaseMapper):
    def databaseList(self):
        sql = 'SHOW DATABASES;'
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        database_list = []
        for i in rows:
            database_list.append(
                {'name': i[0], 'database_id': rows.index(i)}
            )
        self.pool.close()
        return database_list

    def tableList(self, dbId: str):
        sql = f"""
        select 
            table_name as 'tableName', 
            table_comment as 'tableComment'
        from information_schema.TABLES
        where table_schema = '{dbId}';
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        col_names = [desc[0] for desc in self.pool.description]
        database_list = []
        for list in rows:
            tMap = dict(zip(col_names, list))
            database_list.append(tMap)
        self.pool.close()
        return database_list

    def columnList(self, dbName: str, tableName: str):

        sql = f"""
        select 
            COLUMN_NAME columnname,
            DATA_TYPE typename,
            CHARACTER_MAXIMUM_LENGTH typelength,
            COLUMN_COMMENT comment,
            case COLUMN_KEY WHEN 'PRI' THEN 1 else 0 END              isPrimaryKey,
            IS_NULLABLE isNullAble,
            (CASE WHEN EXTRA = 'auto_increment' THEN 1 ELSE 0 END) AS isIdentity,
            NUMERIC_PRECISION 'precision',
            NUMERIC_SCALE 'scale',
            COLUMN_DEFAULT 'defalutDefine'
        from information_schema.columns
        where table_schema = '{dbName}'
        and table_name = '{tableName}';
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        col_names = [desc[0] for desc in self.pool.description]
        database_list = []
        for list in rows:
            tMap = dict(zip(col_names, list))
            database_list.append(tMap)
        self.pool.close()
        return database_list

    def indexList(self, dbName: str, tableName: str, objectId: int = None, indexId: int = None):

        # sql = f"""
        # SELECT
        # *
        # FROM mysql.`innodb_index_stats` a
        # WHERE a.`database_name` = '{dbName}'
        # and a.table_name like '{tableName}';
        # """\

        sql = f"""
        show index from {dbName}.{tableName};
        """
        logger.info(sql)
        self.pool.execute(sql)
        rows = self.pool.fetchall()
        col_names = [desc[0] for desc in self.pool.description]
        database_list = []
        for list in rows:
            tMap = dict(zip(col_names, list))
            database_list.append(tMap)
        self.pool.close()
        return database_list


if __name__ == '__main__':
    mysql_config = CommonSqlConfig(
        configType = 101,
        password = 'NIGlxMSglkmublMHJXBE',
        user = 'songjichao',
        database = 'sp_cust',
        port = 3306,
        host = '114.67.103.225'
    )
    mssql_config = CommonSqlConfig(
        configType = 101,
        password = 'READ433$SKUSER',
        user = 'readuser',
        database = 'MSRosterDB',
        port = 1433,
        host = '114.67.89.195'
    )

    mssql_config2 = CommonSqlConfig(
        configType = 101,
        password = 'READ433$SKUSER',
        user = 'readuser',
        database = 'MSRosterDB',
        port = 1433,
        host = '114.67.89.195'
    )

    # mysql_config.createMysqlPool()
    mssql_config.createMssqlPool()
    mssql_config2.createMssqlPool()

    # my = MysqlMapper(pool = mysql_config.pool)
    # print(my.databaseList())
    # print(my.tableList(dbId = 'sp_cust'))
    # print(my.columnList(dbName = 'sp_cust', tableName = 'cu_access_token'))
    # print(my.indexList(dbName = 'sp_cust', tableName = 'cu_involved_party_role'))

    ms = MssqlMapper(pool = mssql_config.pool)
    ms2 = MssqlMapper(pool = mssql_config2.pool)
    # print(ms2.databaseList())
    # print(ms2.tableList(dbId = 'MSRosterDB'))
    # print(ms.columnList(dbName = 'MSRosterDB', tableName = 'tCompanySchema'))
    print(json.dumps(ms.indexList(dbName = 'MSRosterDB', tableName = 'tEmployee')))
